package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:29
 * @ Description: BuytableDaoImpl
 **/

import com.hy.bean.*;
import com.hy.dao.BuytableDao;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import com.hy.vo.BuyParttableVo;
import com.hy.vo.BuytableVo;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;
import com.hy.utils.DateUtils;
import org.springframework.web.bind.annotation.RequestBody;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

public class BuytableDaoImpl implements BuytableDao {
    QueryRunner qr = new QueryRunner();
    private static BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

    @Override
    public Buytable queryById(Integer id) {
        return null;
    }

    @Override
    public List<Map<String, Object>> queryAllByLimit(String state,Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select b.* from buytable b inner join buy_parttable bp on b.id = bp.buytable_id where b.state = ? \n" +
                "GROUP BY b.id \n" +
                "order by b.id \n" +
                "limit ?,?";
        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler(), state,(page - 1) * limit, limit);
        return query;
    }


    @Override
    public long count(String state) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select count(1) from (select count(1) from buytable b inner join buy_parttable bp on b.id = bp.buytable_id where b.state = ?\n" +
                "                GROUP BY b.id \n" +
                "                order by b.id\n" +
                ") t1";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>(),state);
        return query;
    }

    @Override
    public long countDetails(String state, String status,Integer id) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select count(1) \n" +
                "from buytable b \n" +
                "left join buy_parttable bp on b.id = bp.buytable_id \n" +
                "left join carpartstable c on c.id = bp.parttable_id\n" +
                "inner join producertable p on p.id = c.producertable_id\n" +
                "where state=? and b.id = ?\n";
        if(state == "2"){
            sql+="AND c.partPlaceCode != ''";
        }else if(state == "3"){
            sql+="AND c.partPlaceCode = ''";
        }
        Long query = qr.query(conn, sql, new ScalarHandler<Long>(),status, id);
        return query;
    }

    @Override
    public long countStatis(String buyNumber,String buyer, String producername, String buyTime, String reachTime) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "SELECT count(*) \n" +
                "FROM buytable b \n" +
                "LEFT JOIN buy_parttable bp ON b.id = bp.buytable_id \n" +
                "LEFT JOIN carpartstable c ON c.id = bp.parttable_id\n" +
                "INNER JOIN producertable p ON p.id = c.producertable_id\n" +
                "WHERE b.buyer LIKE ? \n" +
                "  AND p.producername LIKE ? \n"+
                "  AND b.buyNumber LIKE ? \n";
        if ((buyTime != null && buyTime.trim() != "") && (reachTime != null && reachTime.trim() != "")) {
            sql += "AND b.buyTime BETWEEN ? AND ? \n" +
                    "  AND b.reachTime BETWEEN ? AND ? ";
        }
        Long query = 0l;
        if ((buyTime != null && buyTime.trim() != "") && (reachTime != null && reachTime.trim() != "")) {
            query = qr.query(conn, sql, new ScalarHandler<Long>(), "%" + buyer + "%", "%" + producername + "%","%"+buyNumber+"%", buyTime, reachTime, buyTime, reachTime);
        } else {
            query = qr.query(conn, sql, new ScalarHandler<Long>(), "%" + buyer + "%", "%" + producername + "%","%"+buyNumber+"%");
        }
        return query;
    }

    @Override
    public int insert(Connection connection, Buytable buytable) {
        String sql = "insert into buytable(buyNumber,requestBuyer,requestTime,state,remark) values(?,?,?,?,?)";
        try {
            BigInteger integer = qr.insert(connection, sql,new ScalarHandler<>(), buytable.getBuynumber(), buytable.getRequestbuyer(), buytable.getRequesttime(), buytable.getState(),buytable.getRemark());
            return integer.intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Connection connection, Buytable buytable) {
        return 0;
    }

    @Override
    public int updateBuyerAndBuyTimeById(Connection connection, Integer id, String name, Date buyTime, Double zje){
        String sql = "update buytable set buyer = ?,buyTime = ?,totalPrice = totalPrice + ?,state = '3' where id = ?";
        String sql2 = "update buytable set totalPrice = totalPrice + ?,state = '2' where id = ?";
        try {
            if (name == null){
                return qr.update(connection,sql2,zje,id);
            }
            return qr.update(connection,sql,name,buyTime,zje,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteById(Integer id) {

        return 0;
    }




    @Override
    public List<Map<String, Object>> queryAllDetails(String state, String status, Integer id, Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select c.id,bp.id bid,requestbuyer,requesttime,partcode,partname,partplacecode,\n" +
                "partunit,producerTable_id,c.partCreater,inStoreCount,reachCount,c.partCreateDate,producername,buycount,costPrice,rebate,bp.totalPrice from buytable b\n" +
                "left join buy_parttable bp on b.id = bp.buytable_id\n" +
                "left join carpartstable c on c.id= bp.parttable_id\n" +
                "inner join producertable p on p.id = c.producertable_id\n" +
                "where b.state = ? and b.id = ?\n";
        if(state == "2"){
            sql+="AND c.partPlaceCode != ''";
        }else if(state == "3"){
            sql+="AND c.partPlaceCode = ''";
        }
        sql += "limit ?,?";
        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler(),status,  id, (page - 1) * limit, limit);
        return query;
    }

    public List<Buytable> selectBuyTableState(String state) {
        List<Buytable> buytableList = null;
        try {
            Connection conn = DBManager.getConnection();

            String sql = "select * from buytable where state = ?";

            buytableList = qr.query(conn, sql, new BeanListHandler<>(Buytable.class, basicRowProcessor), state);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }

        return buytableList;
    }

    @Override
    public List<Map<String, Object>> queryAllStatis(String buyNumber, String buyer, String producername, String buyTime, String reachTime, Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "SELECT b.*, c.partname, bp.buycount, p.producername \n" +
                "FROM buytable b \n" +
                "LEFT JOIN buy_parttable bp ON b.id = bp.buytable_id \n" +
                "LEFT JOIN carpartstable c ON c.id = bp.parttable_id\n" +
                "INNER JOIN producertable p ON p.id = c.producertable_id\n" +
                "WHERE b.buyer LIKE ? \n" +
                "  AND p.producername LIKE ? \n"+
                "  AND b.buyNumber LIKE ? \n";
        if ((buyTime != null && buyTime.trim() != "") && (reachTime != null && reachTime.trim() != "")) {
            sql += "AND b.buyTime BETWEEN ? AND ? \n" +
                    "  AND b.reachTime BETWEEN ? AND ? ";
        }
        sql += "LIMIT ?, ?";
        List<Map<String, Object>> query = new ArrayList<>();
        if ((buyTime != null && buyTime.trim() != "") && (reachTime != null && reachTime.trim() != "")) {
            query = qr.query(conn, sql, new MapListHandler(), "%" + buyer + "%", "%" + producername + "%", "%"+buyNumber+"%",buyTime, reachTime, buyTime, reachTime, (page - 1) * limit, limit);
        } else {
            query = qr.query(conn, sql, new MapListHandler(), "%" + buyer + "%", "%" + producername + "%","%"+buyNumber+"%", (page - 1) * limit, limit);
        }
        return query;
    }

    @Override
    public List<Buytable> selectAll(){
        Connection connection = null;
        String sql = "select * from buytable where state = '0'";
        try {
            connection = DBManager.getConnection();
            List<Buytable> query = qr.query(connection, sql, new BeanListHandler<>(Buytable.class, basicRowProcessor));
            return query;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public BuytableVo selectById(Integer id){
        Connection connection = null;
        String sql = "select * from buytable where id = ?";
        String sql2 = "select part.*,car.partName from buy_parttable part join carpartstable car on part.partTable_id = car.id  where buytable_id = ?";
        try {
            connection = DBManager.getConnection();
            BuytableVo query = qr.query(connection, sql, new BeanHandler<>(BuytableVo.class, basicRowProcessor), id);
            List<BuyParttableVo> query2 = qr.query(connection, sql2, new BeanListHandler<>(BuyParttableVo.class, basicRowProcessor), query.getId());
            query.setChild(query2);
            return query;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public Buytable selectByRequestTime(String requestTime) {
        Buytable buytable = null;
        try {
            Connection conn = DBManager.getConnection();

            String sql = "select * from buytable where requesttime = ?";

            buytable = qr.query(conn, sql, new BeanHandler<>(Buytable.class, basicRowProcessor), requestTime);

            DbUtils.close(conn);
        } catch (Exception e) {
            throw new BizException("数据库连接异常", e);
        }

        return buytable;
    }

    @Override
    public boolean updateState(Integer id,String state, Connection conn) throws Exception {
        String sql = "update buytable set state = ? where id = ?";
        int update = qr.update(conn, sql, state,id);
        return update > 0 ? true : false;
    }

    @Override
    public boolean updateInfo(Integer id, String Info, Connection conn) throws Exception {
        String sql = "update buytable set managerFeedbackinfo = ?,state = '1' where id = ?";
        int update = qr.update(conn, sql, Info, id);
        return update > 0 ? true : false;
    }

    @Override
    public boolean updateUser(Integer id, Integer person, String time, Integer person1, String time1, Connection conn) throws Exception {
        String sql = "update buytable set managerCheckPerson = ?,managerCheckTime = ?,lastUpdatePerson = ?,lastUpdateTime= ? where id = ?";
        int update = qr.update(conn, sql, person, time, person1, time1, id);
        return update > 0 ? true : false;
    }


    @Override
    public List<Producertable> queryAllVendor() throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select * from producertable";
        List<Producertable> query = qr.query(conn, sql, new BeanListHandler<Producertable>(Producertable.class));
        return query;
    }

    @Override
    public long countCar(String name) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select count(1) from carpartstable where partName = ?";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>(),name);
        return query;
    }

    @Override
    public int insertCar(Carpartstable car,Connection conn) throws SQLException {
        String sql = "INSERT INTO `carpartstable` (`partCode`, `partName`, `partCreater`, `partCreateDate`, `partPlaceCode`, `producerTable_id`, `partUnit`, `partCubage`, `partWeight`, `partStore`, `partMinStore`, `salePrice`, `costPrice`, `rebate`, `details`) VALUES ( ?, ?, ?, ?, '', ?, ?, ?, ?, ?, ?, ?, ?,?, ?);";
        BigInteger update = qr.insert(conn, sql,new ScalarHandler<BigInteger>(), car.getPartcode(), car.getPartname(), car.getPartcreater(), DateUtils.getNowTime(),car.getProducertableId(), car.getPartunit(), car.getPartcubage(), car.getPartweight(), car.getPartstore(), car.getPartminstore(), car.getSaleprice(), car.getCostprice(), car.getRebate(), car.getDetails());
        return update.intValue();
    }

    @Override
    public int insertBuyPart(BuyParttable buyParttable,Connection conn) throws SQLException {
        String sql = "INSERT INTO `buy_parttable` ( `buyTable_id`, `partTable_id`, `buyCount`, `factoryRebate`, `totalPrice`) VALUES (?, ?, ?, ?, ?);";
        int update = qr.update(conn, sql,buyParttable.getBuytableId(),buyParttable.getParttableId(),buyParttable.getBuycount(),buyParttable.getFactoryrebate(),buyParttable.getTotalprice());
        return update;
    }

    @Override
    public int updateBuy(Connection conn, Double totalPrice, Integer id, Integer buyCount) throws SQLException {
        String sql = "update buy_parttable set totalPrice = ? , buyCount = ? where id = ?";
        int update = qr.update(conn, sql,totalPrice,buyCount,id);
        return update;
    }

    @Override
    public int updateCar(Connection conn, Double costPrice, Integer id,String partDetailChanger) throws SQLException {
        String sql = "update carpartstable set costPrice = ?,partDetailChanger = ? where id = ?";
        int update = qr.update(conn, sql,costPrice,partDetailChanger,id);
        return update;
    }

    @Override
    public int updateCode(String code, Integer id, String name) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "update carpartstable set partPlaceCode = ?,partDetailChanger = ? where id = ?";
        int update = qr.update(conn, sql,code,name,id);
        return update;
    }

    @Override
    public boolean deleteCarPart(Integer id, Connection conn) throws SQLException {

        String sql = "delete from carpartstable where id = ?";
        int update = qr.update(conn, sql,id);
        return update>0?true:false;
    }

    @Override
    public boolean deleteBuyPart(Integer id, Connection conn) throws SQLException {
        String sql = "delete from buy_parttable where id = ?";
        int update = qr.update(conn, sql,id);
        return update>0?true:false;
    }

    //查询供应商
    @Override
    public List<Producertable> listProducertable(Integer page, Integer limit) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select * from producertable limit ?,?";
        List<Producertable> query = qr.query(conn, sql, new BeanListHandler<Producertable>(Producertable.class,basicRowProcessor), (page - 1) * limit, limit);

        return query;
    }
    @Override
    public long countProducertable() throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "select count(1) from producertable ";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>());
        return query;
    }
    @Override
    public int addProducertable(Producertable producertable) throws Exception {
        Connection conn = DBManager.getConnection();
        String sql = "INSERT INTO `producertable` (`producerName`,   `linkman`, `phoneNumber`, `address`, `remark`) VALUES ( ?, ?, ?, ?, ?);";
        int update = qr.update(conn, sql,producertable.getProducerName(),producertable.getLinkman(),producertable.getPhoneNumber(),producertable.getAddress(),producertable.getRemark()  );
        return update;
    }

    public int updateProducertable(@RequestBody Producertable producertable) throws Exception{
        Connection conn = DBManager.getConnection();
        String sql = "update producertable set producerName = ?,linkman = ?,phoneNumber = ?,address = ?,remark = ? where id = ?";
        int update = qr.update(conn, sql,producertable.getProducerName(),producertable.getLinkman(),producertable.getPhoneNumber(),producertable.getAddress(),producertable.getRemark(),producertable.getId());
        return update;
    }
    public int deleteProducertable(Integer id) throws Exception{
        Connection conn = DBManager.getConnection();
        String sql = "delete from producertable where id = ?";
        int update = qr.update(conn, sql,id);
        return update;
    }
    public int updateUserTime(Integer id,int userId,String time,Connection conn) throws SQLException {
        String sql = "update buytable set lastUpdatePerson = ?,lastUpdateTime= ? where id = ?";
        int update = qr.update(conn, sql,userId,time,id);
        return update;
    }

    @Override
    public int updateBuyTotalPrice(Connection conn, Integer id) throws SQLException {
        String sql = "UPDATE buytable\n" +
                "SET totalPrice = (\n" +
                "    SELECT totalPrice_sum\n" +
                "    FROM (\n" +
                "        SELECT SUM(bp.totalPrice) AS totalPrice_sum\n" +
                "        FROM buytable b\n" +
                "        INNER JOIN buy_parttable bp ON b.id = bp.buyTable_id\n" +
                "        WHERE b.id = ?\n" +
                "    ) AS subquery\n" +
                ")\n" +
                "WHERE id = ?;\n";
        int update = qr.update(conn,sql,id,id);
        return update;
    }
}
